Applying SORT and SORTBY Functions

Course: Advanced Excel

Topic: Applying SORT and SORTBY Functions

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to SORT and SORTBY Functions

Sorting is an essential data management operation in Microsoft Excel. It arranges data in a specific order, such as alphabetical order, numerical order, chronological order, or ranking order. Sorting makes large datasets easier to read, compare, analyse, and interpret.

Excel provides several sorting methods, including the Sort command on the Data tab and the dynamic array functions SORT and SORTBY. These functions allow users to create formulas that return automatically sorted results without manually rearranging the original dataset.

The SORT function sorts an array based on the values in a specified column or row within that array. The SORTBY function sorts an array based on one or more corresponding ranges or arrays, even when the sorting criteria are not part of the returned array.

These functions are useful for sales reports, employee rankings, inventory analysis, student results, financial summaries, and interactive dashboards.

2. Learning Objectives

• Understand the purpose of the SORT and SORTBY functions.
• Learn the syntax and arguments of both functions.
• Sort text, numbers, dates, and tables in ascending or descending order.
• Sort data using a specific column or row.
• Sort records using multiple criteria.
• Understand dynamic array behaviour and spill ranges.
• Combine SORT and SORTBY with FILTER and other Excel functions.
• Identify common errors and apply sorting best practices.

3. What Is the SORT Function?

The SORT function returns a sorted version of an array or range. It does not rearrange the original source data. Instead, it generates a separate sorted result that updates automatically when the underlying data changes, subject to the formula's source range.

Syntax:

=SORT(array,[sort_index],[sort_order],[by_col])

4. Understanding the SORT Arguments

4.1 array

The array argument identifies the data that needs to be sorted. It can be a single column, a single row, or a multi-column table.

Example:

=SORT(A2:A6)

This formula returns a sorted version of the values in A2:A6.

4.2 sort_index

The sort_index specifies which column or row within the selected array determines the sorting order. When sorting a multi-column table by its second column, use 2.

Example:

=SORT(A2:C6,2,1)

This sorts the rows of A2:C6 by the second column in ascending order. The entire rows move together in the returned array.

4.3 sort_order

The sort_order argument determines whether the results are arranged in ascending or descending order.

• 1: Ascending order, such as A to Z or smallest to largest.
• -1: Descending order, such as Z to A or largest to smallest.

Example:

=SORT(A2:A10,1,-1)

This sorts the values in A2:A10 in descending order.

4.4 by_col

The by_col argument determines whether Excel sorts by rows or columns.

• FALSE: Sorts the rows based on values in the selected column. This is the default.
• TRUE: Sorts columns based on values in the selected row.

This argument is particularly useful when data is arranged horizontally instead of vertically.

5. What Is the SORTBY Function?

SORTBY sorts an array based on values in one or more separate arrays. This is useful when the sorting criteria are stored outside the range that you want to return.

Syntax:

=SORTBY(array,by_array1,[sort_order1],[by_array2,sort_order2],...)

SORTBY is particularly useful when you want to return employee names and departments sorted by sales figures, even though the sales figures themselves do not need to be included in the displayed result.

6. Difference Between SORT and SORTBY

7. Example Dataset for Sorting

Consider the following employee sales dataset stored in cells A1:C6.

Using SORT and SORTBY, you can create sorted reports without changing the original dataset.

8. Sorting Employee Names Alphabetically with SORT

To arrange employee names in alphabetical order, use:

=SORT(A2:A6)

The formula returns the names in ascending alphabetical order.

To arrange the names in reverse alphabetical order, use:

=SORT(A2:A6,1,-1)

Explanation:

1. A2:A6 identifies the employee names.
2. The sort_index of 1 identifies the first column in the selected array.
3. The sort_order of -1 specifies descending order.
4. Excel returns a new sorted list in the formula's spill range.

9. Sorting a Complete Table by Sales

When sorting a multi-column dataset, use the SORT function to arrange the complete rows according to the sales column.

Formula for ascending sales:

=SORT(A2:C6,3,1)

This sorts the complete dataset by the third column, Sales, from smallest to largest.

Formula for descending sales:

=SORT(A2:C6,3,-1)

This sorts employees from the highest sales amount to the lowest sales amount.

Sorting the entire range ensures that employee names, departments, and sales amounts remain correctly associated with one another.

10. Sorting Data Using SORTBY

SORTBY allows you to sort one array using values stored in a separate range.

To return employee names in descending order of sales, use:

=SORTBY(A2:A6,C2:C6,-1)

The formula returns the employee names ordered by their corresponding sales figures, from highest to lowest, without including the sales column in the output.

To return both employee names and departments in the same order, use:

=SORTBY(A2:B6,C2:C6,-1)

This formula sorts the two-column result using the sales values in C2:C6.

11. Sorting by Multiple Criteria with SORTBY

One of the main advantages of SORTBY is its ability to sort by multiple criteria. For example, you may want to sort employees first by department in ascending alphabetical order and then by sales in descending order within each department.

Formula:

=SORTBY(A2:C6,B2:B6,1,C2:C6,-1)

Explanation:

• A2:C6 is the complete dataset being returned.
• B2:B6 is the primary sorting range for department names.
• 1 sorts departments in ascending order.
• C2:C6 is the secondary sorting range for sales.
• -1 sorts sales in descending order within each department.

This approach is useful for departmental sales rankings, product reports, regional performance analysis, and employee evaluations.

12. Sorting Numerical Data

SORT can arrange numerical values in ascending or descending order. This is useful for analysing expenses, sales totals, marks, quantities, and profit figures.

Suppose cells A2:A7 contain these values:

To sort the numbers from smallest to largest, use:

=SORT(A2:A7,1,1)

To sort the numbers from largest to smallest, use:

=SORT(A2:A7,1,-1)

Sorting values makes it easier to identify minimums, maximums, and relative performance.

13. Sorting Dates Chronologically

Excel stores valid dates as serial numbers, which allows SORT and SORTBY to arrange them chronologically. Dates should be stored as actual Excel dates rather than inconsistent text strings.

Suppose cells A2:B6 contain employee names and joining dates.

To sort the complete dataset by joining date from earliest to latest, use:

=SORT(A2:B6,2,1)

To sort by joining date from latest to earliest, use:

=SORT(A2:B6,2,-1)

You can also use SORTBY to sort the employee names using the date column:

=SORTBY(A2:A6,B2:B6,1)

This technique is useful for employee records, invoice tracking, project timelines, delivery schedules, and transaction histories.

14. Sorting Horizontal Data

Sometimes data is arranged horizontally, with values spread across columns rather than down rows. The SORT function can sort horizontally by setting the by_col argument to TRUE.

Suppose cells A1:F1 contain these values:

To sort the values horizontally in ascending order, use:

=SORT(A1:F1,1,1,TRUE)

To sort them horizontally in descending order, use:

=SORT(A1:F1,1,-1,TRUE)

The TRUE argument tells Excel to sort columns based on the values in the selected row. This is useful for horizontal reports, month-based summaries, and comparison tables.

15. Sorting Data with FILTER and SORT

The FILTER function can be combined with SORT to display only records that meet a condition and then sort the filtered results.

For example, to return employees in the Sales department and sort them by sales from highest to lowest, use:

=SORT(FILTER(A2:C6,B2:B6="Sales"),3,-1)

The FILTER function first selects the Sales department records. SORT then orders those records by the third column of the filtered result in descending order.

This technique is useful for:

• Displaying only high-performing employees.
• Filtering products by category and sorting by price.
• Reviewing expenses for a selected department.
• Building interactive reports and dashboards.

FILTER is available in supported newer Excel versions, including Microsoft 365.

16. Creating a Dynamic Sales Ranking Report

You can create a dynamic ranking report using SORTBY without manually rearranging the source data.

Suppose the source dataset contains employee names in A2:A6, departments in B2:B6, and sales values in C2:C6.

Use this formula to display the full dataset from highest to lowest sales:

=SORTBY(A2:C6,C2:C6,-1)

When the source sales figures change, the sorted output updates automatically. The original dataset remains in its original order.

Benefits:

• Reduces manual sorting work.
• Maintains the original data arrangement.
• Automatically reflects changes to source values within the referenced range.
• Supports live reporting and performance comparisons.
• Can be combined with conditional formatting for visual analysis.

17. Understanding Dynamic Arrays and Spill Ranges

SORT and SORTBY are dynamic array functions in supported Excel versions. Instead of returning a single value, they can return multiple rows and columns that spill into neighbouring cells.

For example:

=SORT(A2:C6,3,-1)

This formula can return the complete sorted table into a spill range covering several rows and columns.

Important considerations:

• Enter the formula in a single cell; Excel fills the required output range automatically.
• Keep the spill area clear of existing values.
• If the output range is blocked, Excel may return a #SPILL! error.
• Do not place a spilling formula inside an Excel Table's data body when the function requires the result to spill into neighbouring cells.
• Dynamic formulas recalculate when their source data changes, provided the referenced range includes the changed data.

18. Common SORT and SORTBY Errors

19. Best Practices for SORT and SORTBY

• Use SORT when the sorting criterion is a row or column within the array being returned.
• Use SORTBY when sorting depends on separate ranges or multiple criteria.
• Sort the entire dataset when related columns must remain together.
• Use explicit sort orders to make formulas easier to understand.
• Ensure sorting arrays have compatible dimensions with the array being returned.
• Convert text-formatted numbers and dates before sorting when numeric or chronological order is required.
• Keep spill ranges clear of existing values.
• Use secondary sorting criteria when duplicate primary values need a consistent order.
• Use Excel Tables or suitable expanding ranges when the dataset changes frequently.
• Test formulas with duplicate values, blank cells, and new records.

20. SORT vs. SORTBY vs. Manual Sorting

21. Practical Exercises

Exercise 1: Alphabetical Employee List

Create a list of ten employee names and use SORT to display them in ascending alphabetical order. Then modify the formula to display them in descending order.

Expected formula:

=SORT(A2:A11,1,1)

Exercise 2: Sales Ranking

Create a dataset containing employee names, departments, and sales figures. Use SORT to arrange the complete dataset from highest to lowest sales.

Expected formula:

=SORT(A2:C11,3,-1)

Exercise 3: Sort by Multiple Criteria

Sort the employee dataset by department alphabetically and then by sales from highest to lowest within each department.

Expected formula:

=SORTBY(A2:C11,B2:B11,1,C2:C11,-1)

Exercise 4: Chronological Sorting

Create a list of project names and completion dates. Sort the list from earliest completion date to latest completion date.

Exercise 5: Filter and Sort

Use FILTER to display records for one department and then SORT to arrange those records by sales in descending order.

Expected formula:

=SORT(FILTER(A2:C11,B2:B11="Sales"),3,-1)

Exercise 6: Horizontal Sorting

Enter six numbers across a single row and use SORT with the by_col argument set to TRUE to arrange them from smallest to largest.

22. Mini Project: Dynamic Employee Performance Dashboard

Project objective: Build a formula-driven employee performance report using SORT and SORTBY.

Project Requirements

• Create an employee dataset with names, departments, sales figures, and performance scores.
• Use SORT to display employees in ascending or descending order based on a selected column.
• Use SORTBY to sort employees by department and then by sales.
• Combine FILTER and SORT to display selected department records.
• Format sales and performance values for readability.
• Keep the original source data unchanged.
• Test the formulas by changing source values and adding records within the referenced range.
• Ensure the dynamic output area remains clear of other worksheet content.

Expected Outcome

The dashboard should display employee records in the required order and update when the underlying data changes. It should support simple rankings, multi-level sorting, and department-specific reports without repeatedly rearranging the original dataset.

23. Interview Questions and Answers

Q1. What is the SORT function in Excel?

Answer: SORT returns an array or range arranged in ascending or descending order based on a specified row or column within that array.

Q2. What is the SORTBY function?

Answer: SORTBY returns an array sorted according to one or more separate sorting arrays.

Q3. What is the difference between SORT and SORTBY?

Answer: SORT uses a numeric sort index to select a row or column within the source array, while SORTBY uses explicit sorting ranges or arrays.

Q4. How do you sort data in descending order?

Answer: Set the sort_order argument to -1.

Q5. Can SORTBY use multiple sorting criteria?

Answer: Yes. Additional sorting arrays and sort-order arguments can be included to establish secondary and further sorting levels.

Q6. What is a dynamic array?

Answer: A dynamic array is a formula result that can return multiple values and automatically spill into neighbouring cells in supported Excel versions.

Q7. Why does #SPILL! occur?

Answer: It can occur when cells in the required output area prevent the dynamic array from expanding.

Q8. Can SORT arrange dates chronologically?

Answer: Yes, provided the dates are stored as valid Excel date values.

Q9. Can SORTBY sort names based on sales figures without displaying the figures?

Answer: Yes. SORTBY can return the names range while using a separate sales range as the sorting criterion.

Q10. Can SORT and SORTBY be combined with FILTER?

Answer: Yes. FILTER can select the required records, and SORT can arrange the resulting array.

24. Quick Revision Checklist

• SORT arranges an array using a selected row or column within that array.
• SORTBY arranges an array using one or more specified sorting arrays.
• Use 1 for ascending order and -1 for descending order.
• Use the by_col argument in SORT to sort horizontally when required.
• Use SORTBY for multi-criteria sorting.
• Use FILTER with SORT to create filtered and sorted reports.
• Dynamic array formulas require sufficient space for their output.
• Ensure that sorting ranges have compatible dimensions.
• Check number and date formats if results appear incorrectly ordered.
• Use expanding ranges or Excel Tables where appropriate for growing datasets.

25. Conclusion

The SORT and SORTBY functions are valuable tools for dynamic data management in Advanced Excel. SORT is ideal for straightforward sorting based on a column or row within an array, while SORTBY provides greater flexibility when sorting by separate ranges or multiple criteria.

By combining these functions with FILTER and dynamic array formulas, you can create automated employee rankings, sales reports, inventory summaries, and interactive dashboards. Practising these techniques helps improve reporting efficiency and reduces the need for repetitive manual sorting.

Learn More with JustAcademy

Explore the Advanced Excel Course to strengthen your Excel formulas, sorting techniques, data analysis, and reporting skills.

Register for a Course Demo to learn more about the training program.

Visit the JustAcademy website to explore additional courses and learning resources.